package com.ats.daoimpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.ats.dao.InstructorDao_xh;
import com.ats.model.Instructor;
import com.ats.model.Student;
import com.ats.util.JDBCUtil;

import net.sf.json.JSONArray;
/**
 * 辅导员个人功能的DAO层实现类
 * @author xh
 *
 */
public class InstructorDaoImpl_xh implements InstructorDao_xh {

	public String FindInameByIid(int iid) {
		// TODO Auto-generated method stub
		String iname ="";
		StringBuffer  sql = new StringBuffer();
		sql.append(" select name from ats_instructor");
		sql.append(" where iid= ?");
		Connection con =JDBCUtil.getConnection();
		PreparedStatement pst =null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setInt(1,iid);
			ResultSet rs = pst.executeQuery();
			if(rs.next()){
				iname=rs.getString("name");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return iname;
	}

	@Override
	public JSONArray FindInstructorByUid(int uid) {
		// TODO Auto-generated method stub
		JSONArray jso = new JSONArray();
		StringBuffer  sql = new StringBuffer();
		sql.append(" select ats_instructor.iid,ats_instructor.`name`,ats_instructor.age,");
		sql.append(" ats_instructor.address,ats_instructor.number,ats_user.uid,");
		sql.append(" ats_user.uname,ats_user.upsw");
		sql.append(" from ats_instructor");
		sql.append(" inner join ats_user");
		sql.append(" on ats_instructor.iid = ats_user.iid");
		sql.append(" where ats_user.uid = ? ");
		Connection con =JDBCUtil.getConnection();
		PreparedStatement pst =null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setInt(1,uid);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				Instructor ins = new Instructor();
				ins.setIid(rs.getInt("iid"));
				ins.setName(rs.getString("name"));
				ins.setAge(rs.getInt("age"));
				ins.setAddress(rs.getString("address"));
				ins.setNumber(rs.getString("number"));
				ins.setUid(rs.getInt("uid"));
				ins.setUname(rs.getString("uname"));
				ins.setUpsw(rs.getString("upsw"));
				jso.add(ins);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return jso;
	}

	@Override
	public boolean updateInstructorByIid(String name, int age, String address, String number,int iid) {
		// TODO Auto-generated method stub
		boolean flag =false;
		StringBuffer  sql = new StringBuffer();
		sql.append("update ats_instructor set ats_instructor.`name`= ?");
		sql.append(",ats_instructor.age=?,ats_instructor.address=?,");
		sql.append("ats_instructor.number=?");
		sql.append(" where iid= ?");
		Connection con =JDBCUtil.getConnection();
		PreparedStatement pst =null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setString(1, name);
			pst.setInt(2, age);
			pst.setString(3, address);
			pst.setString(4, number);
			pst.setInt(5, iid);
			int i =pst.executeUpdate();
			if(i>0){
				flag=true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}

	@Override
	public boolean updateInstructorPswByIid(String uname, String upsw, int iid) {
		// TODO Auto-generated method stub
		boolean flag =false;
		StringBuffer  sql = new StringBuffer();
		sql.append("update ats_user set uname=?,upsw=? ");
		sql.append(" where iid=?");
		Connection con =JDBCUtil.getConnection();
		PreparedStatement pst =null;
		try {
			pst = con.prepareStatement(sql.toString());
			pst.setString(1, uname);
			pst.setString(2, upsw);
			pst.setInt(3, iid);
			int i =pst.executeUpdate();
			if(i>0){
				flag=true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}

}
